﻿/******************************************************************************
'Description: DataTable导出至Excel工具类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:zhunan
'create Date:2018.12.25
 ********************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Net;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
using System.Configuration;
using System.Drawing;
//using NPOI.XSSF.UserModel;

namespace uMES.LeanManufacturing.Common
{
    public class ExportHelper
    {
        /// <summary>
        /// DataTable导出至Excel
        /// </summary>
        /// <param name="SourceTable">源数据</param>
        /// <param name="title">标题</param>
        /// <param name="isSetColumnWidth">是否设置列为固定宽度</param>
        public static void DataTableExportToExcel(DataTable SourceTable, string title, bool isSetColumnWidth = false)
        {
            if (SourceTable.Rows.Count == 0)
            {
                return;
            }

            //PAGE_MAX_ROWS => 每个Sheet页最多能够显示的行数。Office 2003最多可显示65536行
            const int PAGE_MAX_ROWS = 65536;

            //coloumsCount => Excel列数
            int coloumsCount = SourceTable.Columns.Count;

            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();

            #region 单元格样式设置
            ICellStyle style = workbook.CreateCellStyle();

            //水平对齐居中
            style.Alignment = HorizontalAlignment.Center;

            //设置字体
            IFont font = workbook.CreateFont();
            font.FontName = "微软雅黑";
            style.SetFont(font);

            //设置边框
            style.BorderTop = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            #endregion

            #region 表头样式设置
            ICellStyle headerCellStyle = workbook.CreateCellStyle();

            //居中显示
            headerCellStyle.Alignment = HorizontalAlignment.Center;

            //设置边框
            headerCellStyle.BorderTop = BorderStyle.Thin;
            headerCellStyle.BorderBottom = BorderStyle.Thin;
            headerCellStyle.BorderLeft = BorderStyle.Thin;
            headerCellStyle.BorderRight = BorderStyle.Thin;

            //设置背景色
            headerCellStyle.FillForegroundColor = HSSFColor.LightGreen.Index;
            headerCellStyle.FillPattern = FillPattern.SolidForeground;

            //加粗
            IFont headerFont = workbook.CreateFont();
            headerFont.Boldweight = short.MaxValue;
            headerFont.FontName = "微软雅黑";
            headerCellStyle.SetFont(headerFont);
            #endregion

            #region 标题样式设置
            ICellStyle titleCellStyle = workbook.CreateCellStyle();

            //居中显示
            titleCellStyle.Alignment = HorizontalAlignment.Center;

            //设置边框
            titleCellStyle.BorderTop = BorderStyle.Thin;
            titleCellStyle.BorderBottom = BorderStyle.Thin;
            titleCellStyle.BorderLeft = BorderStyle.Thin;
            titleCellStyle.BorderRight = BorderStyle.Thin;

            //设置背景色
            titleCellStyle.FillForegroundColor = HSSFColor.LightGreen.Index;
            titleCellStyle.FillPattern = FillPattern.SolidForeground;

            //加粗
            IFont titleFont = workbook.CreateFont();
            titleFont.Boldweight = short.MaxValue;
            titleFont.FontName = "微软雅黑";

            titleCellStyle.SetFont(titleFont);
            #endregion

            int sheetCount = 1;

            //每个sheet页达到最大行数限制时，自动创建新的sheet页
            for (int i = 0; i < SourceTable.Rows.Count; i += (PAGE_MAX_ROWS-2))
            {
                HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("sheet" + sheetCount.ToString());

                //创建第1行：标题行
                HSSFRow titleRow = (HSSFRow)sheet.CreateRow(0);

                //为正确显示合并居中行的边框，第一行除第一列之外全部插入空值
                for (int j = 0; j < SourceTable.Columns.Count; j++)
                {
                    if (j == 0)
                    {
                        titleRow.CreateCell(j).SetCellValue(title);
                    }
                    else
                    {
                        titleRow.CreateCell(j).SetCellValue("");
                    }
                    titleRow.Cells[j].CellStyle = titleCellStyle;
                }


                //合并第一行单元格作为Excel标题
                //CellRangeAddress四个参数为：起始行位置，结束行位置，起始列位置，结束列位置（位置由0开始算）
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, coloumsCount - 1));

                //创建第2行：表头行
                HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);

                //表头行插值并设置样式 
                foreach (DataColumn column in SourceTable.Columns)
                {
                    if (column.Caption.ToString() != column.ColumnName.ToString())
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
                    }
                    else
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                    }
                    headerRow.Cells[column.Ordinal].CellStyle = headerCellStyle;
                }

                //从第3行开始循环插入实际数据
                int rowIndex = 2;

                for (int k = 0; (k < PAGE_MAX_ROWS - 2) && ((k + i) < SourceTable.Rows.Count); k++)
                {
                    HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);

                    foreach (DataColumn column in SourceTable.Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(SourceTable.Rows[i+k][column].ToString());
                        dataRow.Cells[column.Ordinal].CellStyle = style;
                    }
                    rowIndex++;
                    dataRow = null;
                }

                for (int colIndex = 0; colIndex < SourceTable.Columns.Count; colIndex++)
                {
                    //不设置固定宽度，宽度自适应
                    if (isSetColumnWidth == false)
                        sheet.AutoSizeColumn(colIndex);
                    else
                        sheet.SetColumnWidth(colIndex, 30 * 256);
                }
                
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                sheet = null;
                titleRow = null;
                headerRow = null;
                sheetCount++;
            }

            workbook = null;

            var Response = HttpContext.Current.Response;
            
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}{1}.xls", HttpContext.Current.Server.UrlEncode(title), DateTime.Now.ToString("yyyyMMddHHmmss")));
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            Response.ContentType = "application/ms-excel;charset=UTF-8";
            
            Response.BinaryWrite(ms.ToArray());

            Response.End();
            Response.Flush();
            Response.Close();

            ms.Close();
            ms.Dispose();
        }


        /// <summary>
        /// DataTable导出至Excel,放入临时文件夹
        /// </summary>
        /// <param name="SourceTable">源数据</param>
        /// <param name="title">标题</param>
        /// <param name="isSetColumnWidth">是否设置列为固定宽度</param>
        public static string DataTableExportToExcel2(DataTable SourceTable, string title,string baseUrl, List<int> pigCols=null, bool isSetColumnWidth = false)
        {
            if (SourceTable.Rows.Count == 0)
            {
                return "";
            }

            //PAGE_MAX_ROWS => 每个Sheet页最多能够显示的行数。Office 2003最多可显示65536行
            const int PAGE_MAX_ROWS = 65536;

            //coloumsCount => Excel列数
            int coloumsCount = SourceTable.Columns.Count;

            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();

            #region 单元格样式设置
            ICellStyle style = workbook.CreateCellStyle();

            //水平对齐居中
            style.Alignment = HorizontalAlignment.Center;

            //设置字体
            IFont font = workbook.CreateFont();
            font.FontName = "微软雅黑";
            style.SetFont(font);

            //设置边框
            style.BorderTop = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            #endregion

            #region 表头样式设置
            ICellStyle headerCellStyle = workbook.CreateCellStyle();

            //居中显示
            headerCellStyle.Alignment = HorizontalAlignment.Center;

            //设置边框
            headerCellStyle.BorderTop = BorderStyle.Thin;
            headerCellStyle.BorderBottom = BorderStyle.Thin;
            headerCellStyle.BorderLeft = BorderStyle.Thin;
            headerCellStyle.BorderRight = BorderStyle.Thin;

            //设置背景色
            headerCellStyle.FillForegroundColor = HSSFColor.LightGreen.Index;
            headerCellStyle.FillPattern = FillPattern.SolidForeground;

            //加粗
            IFont headerFont = workbook.CreateFont();
            headerFont.Boldweight = short.MaxValue;
            headerFont.FontName = "微软雅黑";
            headerCellStyle.SetFont(headerFont);
            #endregion

            #region 标题样式设置
            ICellStyle titleCellStyle = workbook.CreateCellStyle();

            //居中显示
            titleCellStyle.Alignment = HorizontalAlignment.Center;

            //设置边框
            titleCellStyle.BorderTop = BorderStyle.Thin;
            titleCellStyle.BorderBottom = BorderStyle.Thin;
            titleCellStyle.BorderLeft = BorderStyle.Thin;
            titleCellStyle.BorderRight = BorderStyle.Thin;

            //设置背景色
            titleCellStyle.FillForegroundColor = HSSFColor.LightGreen.Index;
            titleCellStyle.FillPattern = FillPattern.SolidForeground;

            //加粗
            IFont titleFont = workbook.CreateFont();
            titleFont.Boldweight = short.MaxValue;
            titleFont.FontName = "微软雅黑";

            titleCellStyle.SetFont(titleFont);
            #endregion

            int sheetCount = 1;

            //每个sheet页达到最大行数限制时，自动创建新的sheet页,i：sheet页数
            for (int i = 0; i < SourceTable.Rows.Count; i += (PAGE_MAX_ROWS - 2))
            {
                HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("sheet" + sheetCount.ToString());

                //创建第1行：标题行
                HSSFRow titleRow = (HSSFRow)sheet.CreateRow(0);

                //为正确显示合并居中行的边框，第一行除第一列之外全部插入空值
                for (int j = 0; j < SourceTable.Columns.Count; j++)
                {
                    if (j == 0)
                    {
                        titleRow.CreateCell(j).SetCellValue(title);
                    }
                    else
                    {
                        titleRow.CreateCell(j).SetCellValue("");
                    }
                    titleRow.Cells[j].CellStyle = titleCellStyle;
                }


                //合并第一行单元格作为Excel标题
                //CellRangeAddress四个参数为：起始行位置，结束行位置，起始列位置，结束列位置（位置由0开始算）
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, coloumsCount - 1));

                //创建第2行：表头行
                HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);

                //表头行插值并设置样式 
                foreach (DataColumn column in SourceTable.Columns)
                {
                    if (column.Caption.ToString() != column.ColumnName.ToString())
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
                    }
                    else
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                    }
                    headerRow.Cells[column.Ordinal].CellStyle = headerCellStyle;
                }

                //从第3行开始循环插入实际数据
                int rowIndex = 2;
                               

                for (int k = 0; (k < PAGE_MAX_ROWS - 2) && ((k + i) < SourceTable.Rows.Count); k++)
                {
                    HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);//行

                    foreach (DataColumn column in SourceTable.Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(SourceTable.Rows[i + k][column].ToString());
                        dataRow.Cells[column.Ordinal].CellStyle = style;

                        if (pigCols != null && pigCols.Contains(column.Ordinal))
                        {
                            dataRow.Cells[column.Ordinal].SetCellValue("");
                            AddPictureToCell(dataRow.Cells[column.Ordinal], SourceTable.Rows[i + k][column].ToString(), workbook, sheet);
                        }
                    }
                    rowIndex++;
                    dataRow = null;
                }

                for (int colIndex = 0; colIndex < SourceTable.Columns.Count; colIndex++)
                {
                    if (pigCols != null&&pigCols.Contains(colIndex))
                        continue;
                    //不设置固定宽度，宽度自适应
                    if (isSetColumnWidth == false)
                        sheet.AutoSizeColumn(colIndex);
                    else
                        sheet.SetColumnWidth(colIndex, 30 * 256);
                }


                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                sheet = null;
                titleRow = null;
                headerRow = null;
                sheetCount++;
            }

            workbook = null;

            string suffix = DateTime.Now.ToString("yyyyMMddHHmmddss");
            string strPath = baseUrl+ "/ExportFile/" + title+suffix+".xls";

            FileStream fs = new FileStream(strPath,System.IO.FileMode.Create);

            Byte[] Buffer = ms.ToArray();
            fs.Write(Buffer, 0, Buffer.Length);
            fs.Flush();
            fs.Close();

            ms.Close();
            ms.Dispose();

            return strPath.Replace(baseUrl,""); ;
        }

        static void AddPictureToCell(ICell cell, string pigPath, HSSFWorkbook workbook, HSSFSheet sheet)
        {
            //生成一个新的图片
         //  var pigPath = MerageImagsAndStrs(para);

            //将图片文件读入一个字符串
            byte[] bytess = System.IO.File.ReadAllBytes(pigPath);
            int pictureIdx = workbook.AddPicture(bytess, PictureType.JPEG);
            HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
            
            //前四个参数(dx1,dy1,dx2,dy2)为图片在单元格的边距
            //col1,col2表示图片插在col1和col2之间的单元格，索引从0开始
            //row1,row2表示图片插在第row1和row2之间的单元格，索引从1开始
            // 参数的解析: HSSFClientAnchor（int dx1,int dy1,int dx2,int dy2,int col1,int row1,int col2,int row2)
            //dx1:图片左边相对excel格的位置(x偏移) 范围值为:0~1023;即输100 偏移的位置大概是相对于整个单元格的宽度的100除以1023大概是10分之一
            //dy1:图片上方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。
            //dx2:图片右边相对excel格的位置(x偏移) 范围值为:0~1023; 原理同上。
            //dy2:图片下方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。
            //col1和row1 :图片左上角的位置，以excel单元格为参考,比喻这两个值为(1,1)，那么图片左上角的位置就是excel表(1,1)单元格的右下角的点(A,1)右下角的点。
            //col2和row2:图片右下角的位置，以excel单元格为参考,比喻这两个值为(2,2)，那么图片右下角的位置就是excel表(2,2)单元格的右下角的点(B,2)右下角的点。
            int colIndex = cell.ColumnIndex;int rowIndex = cell.RowIndex;
            System.Drawing.Image img = System.Drawing.Image.FromFile(pigPath);
            int strWidth = img.Width / 10>255?255 : img.Width / 10;
            if (sheet.GetColumnWidth(colIndex) < 256*strWidth) {
                sheet.SetColumnWidth(colIndex, 256 * strWidth);
            }
                       
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, colIndex, rowIndex, colIndex + 1, rowIndex + 1);
            //把图片插到相应的位置
            HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
            //pict.Resize();


        }

       /// <summary>
       /// 合并图片和文字信息为一整个图片
       /// </summary>
       /// <param name="para">图片和文字队列</param>
       /// <param name="fixedWidth">固定宽度</param>
       /// <returns></returns>
        public static string MerageImagsAndStrs(List<object> para,int fixedWidth=0)
        {
            string resultPath = "", filaName = "image-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff");
            int fontSize = 10;int fontPx = 7;//10号字体中文大约占14px，英文大约占7px

            int bmpWidth=0; int bmpHeight = 20;//画板宽度，高度
            int addHeight = 20;//增量高度
            int addCount = 0;//增加过高度的次数
            if (fixedWidth > 0)
            { 
                bmpWidth = fixedWidth;
            }

            int usedWidth = 0, usedHeight =0;

            for(int i=0;i<para.Count;i++) {
                var item = para[i];
                if (item.GetType() == typeof(System.Drawing.Bitmap))
                {
                    System.Drawing.Image img = (System.Drawing.Image)item;

                    if (fixedWidth == 0)
                        bmpWidth += img.Width;
                    else {
                        usedHeight += addHeight;
                        usedWidth = 0;
                        //if (usedWidth + img.Width+1 <= fixedWidth)
                        //{
                        //    usedWidth += img.Width+1;
                        //}
                        //else {
                        //    usedHeight += addHeight;
                        //    usedWidth = 0;
                        //}
                    }
                }
                else if (item.GetType() == typeof(string)) {
                    int strPixelLength = System.Text.Encoding.Default.GetBytes(item.ToString()).Length * fontPx;//字符串所占用的像素长度 
                    if (fixedWidth == 0)
                        bmpWidth += strPixelLength;
                    else
                    {
                      
                        if (usedWidth + strPixelLength <= fixedWidth)
                        {
                            usedWidth += strPixelLength;
                        }
                        else
                        {
                            //
                            int addNum = (int)Math.Ceiling((usedWidth + strPixelLength) / float.Parse((fixedWidth).ToString()));
                            if (addCount == 0)
                                usedHeight += addHeight * (addNum - 1);
                            else
                                usedHeight += addHeight * (addNum);
                            usedWidth = 0;
                            addCount++;
                        }
                    }
                }
            }
            bmpHeight += usedHeight;

            //创建位图并获取绘图设备
            Bitmap objBmp = new Bitmap(bmpWidth, bmpHeight);                       
            System.Drawing.Graphics graphics = System.Drawing.Graphics.FromImage(objBmp);
            graphics.Clear(Color.White);//背景默认为黑色，需要用白色替换一下
            //重置已使用宽度高度
            usedWidth = 0; usedHeight = 0; addCount = 0;

            foreach (var item in para)
            {
                if (item.GetType() == typeof(System.Drawing.Bitmap))
                {
                    System.Drawing.Image img = (System.Drawing.Image)item;

                    if (fixedWidth == 0)
                    {
                        graphics.DrawImage(img, usedWidth, 1, img.Width, 20);
                        usedWidth += img.Width;
                    }
                    else
                    {
                       // usedHeight += addHeight; usedWidth = img.Width + 1;
                       // graphics.DrawImage(img, 0, usedHeight, img.Width, 20);
                        if (usedWidth + img.Width + 1 <= fixedWidth)
                        {
                            graphics.DrawImage(img, usedWidth, usedHeight, img.Width, 20);
                            usedWidth += img.Width + 1;
                        }
                        else
                        {
                            usedHeight += addHeight; usedWidth = img.Width + 1;
                            graphics.DrawImage(img, 0, usedHeight, img.Width, 20);
                        }
                    }

                }
                else if (item.GetType() == typeof(string))
                {
                    int strPixelLength = System.Text.Encoding.Default.GetBytes(item.ToString()).Length * fontPx;//字符串所占用的像素长度 System.Text.Encoding.UTF8.GetBytes(strPadleft).Length
                    
                    if (fixedWidth == 0)
                    {
                        graphics.DrawString(item.ToString(), new System.Drawing.Font("宋体", fontSize), new System.Drawing.SolidBrush(System.Drawing.Color.Red), new System.Drawing.Point(usedWidth, 0));
                        usedWidth += strPixelLength;
                    }
                    else
                    {
                        #region 自动换行
                        //RectangleF rec = new RectangleF(usedWidth, usedHeight, fixedWidth-usedWidth, bmpHeight);
                        //RectangleF rec = new RectangleF(0, usedHeight, fixedWidth, bmpHeight); StringFormat sf = new StringFormat(); sf.LineAlignment = StringAlignment.Near;
                        //string strPadleft = "";
                        //while (System.Text.Encoding.Default.GetBytes(strPadleft).Length * fontSize <= usedWidth) {
                        //    strPadleft += " ";
                        //}
                        //strPadleft += "  ";

                        //float test = graphics.MeasureString(strPadleft, new System.Drawing.Font("宋体", fontSize)).Width;

                        //graphics.DrawString(strPadleft+item.ToString(), new System.Drawing.Font("宋体", fontSize), new System.Drawing.SolidBrush(System.Drawing.Color.Red), rec,sf);
                        #endregion

                        #region 文字，换行计算
                        foreach (var s in item.ToString()) {
                            int sl= System.Text.Encoding.Default.GetBytes(s.ToString()).Length * fontPx;//计算每个字符所占的长度
                            if (usedWidth + sl + 1 <= fixedWidth)
                            {
                                graphics.DrawString(s.ToString(), new System.Drawing.Font("宋体", fontSize), new System.Drawing.SolidBrush(System.Drawing.Color.Red),
                                    new System.Drawing.Point(usedWidth, usedHeight));
                                usedWidth += sl;
                            }
                            else {
                                usedWidth = 0;usedHeight += addHeight;
                                graphics.DrawString(s.ToString(), new System.Drawing.Font("宋体", fontSize), new System.Drawing.SolidBrush(System.Drawing.Color.Red),
                                    new System.Drawing.Point(usedWidth, usedHeight));
                                usedWidth += sl;
                            }
                        }

                        #endregion
                        
                    }

                }
            }

           // System.Drawing.Image myImage = System.Drawing.Image.FromFile(imgPath);                     

            resultPath = HttpRuntime.AppDomainAppPath + ConfigurationManager.AppSettings["ImageTempPath"] + filaName;
            objBmp.Save(resultPath, System.Drawing.Imaging.ImageFormat.Jpeg);
            graphics.Dispose();

            return resultPath;
        }
    }
}
